<?php
declare (strict_types = 1);

namespace app\webadmin\controller;

use app\webadmin\BaseController;
use app\webadmin\model\JingmiduCg;
use app\webadmin\validate\JingmiduCheck;
use PhpOffice\PhpSpreadsheet\Cell\DataType;
use PhpOffice\PhpSpreadsheet\Style\Color;
use PhpOffice\PhpSpreadsheet\Style\Font;
use think\facade\Db;
use think\facade\View;
use \think\facade\Log;
use think\exception\ValidateException;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Calculation\Statistical;
class Jingmidu extends BaseController
{
    public function index()
    {
        if (request()->isAjax()) {
            $param = get_params();
            $where = array();
            if (!empty($param['keywords'])) {
                $where[] = ['id|nums|data1', 'like', '%' . $param['keywords'] . '%'];
            }
            $where[] = ['uid', '=', get_login_admin('id')];
            $rows = empty($param['limit']) ? get_config('app.page_size') : $param['limit'];
            $content = JingmiduCg::where($where)
                ->order('id asc')
                ->paginate($rows, false, ['query' => $param])->each(function ($item, $k) {
                    //$item['createtime']=date('Y-m-d H:i:s',$item['create_time']);
                    $item['data1'] = sprint_data(floatval($item['data1']), 3);
                    $item['data2'] = sprint_data(floatval($item['data2']), 3);
                    $item['xiangduizhi'] = sprint_data(floatval($item['xiangduizhi']), 3);
                    $item['jueduizhi'] = sprint_data(floatval($item['jueduizhi']), 3);
                    $item['avgs'] = sprint_data(floatval($item['avgs']), 3);

                    return $item;
                });
            return table_assign(0, '', $content);
        } else {
            return view();
        }
    }

    //添加/修改
    public function add(){
        $param = get_params();
        if (request()->isAjax()) {
            if (!empty($param['id']) && $param['id'] > 0) {
                //修改
                try {
                    validate(JingmiduCheck::class)->scene('edit')->check($param);
                } catch (ValidateException $e) {
                    // 验证失败 输出错误信息
                    return to_assign(1, $e->getError());
                }
                $param['update_time'] = time();
                $res = JingmiduCg::strict(false)->field(true)->update($param);
                if ($res) {
                    add_log('edit', $param['id'], $param);
                    return to_assign(0, "修改成功");
                }
                return to_assign();
            } else {
                //新增
                try {
                    validate(JingmiduCheck::class)->scene('add')->check($param);
                } catch (ValidateException $e) {
                    // 验证失败 输出错误信息
                    return to_assign(1, $e->getError());
                }
                $param['create_time'] = time();
                $param['uid']=get_login_admin('id');
                $insertId = JingmiduCg::strict(false)->field(true)->insertGetId($param);
                if ($insertId) {
                    add_log('add', $insertId, $param);
                    return to_assign(0, "添加成功");
                }
                return to_assign();
            }
        }
        else{
            $id = isset($param['id']) ? $param['id'] : 0;
            if ($id > 0) {
                $wdx = JingmiduCg::where(['id' => $id])->find();
                if($wdx){
                }
                View::assign('data', $wdx);
            }
            View::assign('id', $id);
            return view();
        }
    }

    //start_jisuan
    public function start_jisuan(){
        $where=[];
        $where[]=['uid','=',get_login_admin('id')];
        $jingdu=JingmiduCg::where($where)->order('id asc')->select();
        $i=0;
        $j=0;
        $six_array=[];
        $ten_array=[];
        $jingdu1=$jingdu;
        foreach($jingdu1 as $key=>$v){
            $i++;
            if($i<=6){
                $six_array[]=$v['jueduizhi'];
            }else{
                continue;
            }
        }
        foreach($jingdu as $key=>$v){
            $j++;
            if($j>=7){
                $ten_array[]=$v['xiangduizhi'];
            }else{
                continue;
            }
        }
        //=AVERAGE(D4:D9)
        $six_avg=sprint_data(Statistical::AVERAGE($six_array),3);
        $guding=1.128;//固定值
        //=I6/I7
        $hdbzc=sprint_data($six_avg/$guding,3);

        $ten_avg=sprint_data(Statistical::AVERAGE($ten_array),3);
        //=AVERAGE(F10:F18)
        //=I11/I7
        $xdhdbzc=sprint_data($ten_avg/$guding,3);

        $jyhl=sprint_data(1.5003,3);
        //=(I8^2+(I12*I15)^2)^0.5
        $clbqdd=pow(pow($hdbzc,2)+pow(($xdhdbzc*$jyhl),2),0.5);
        $clbqdd=sprint_data($clbqdd,3);


        $html = '<table class="layui-table layui-table-form">';
        $html .= "<tr><td align=\"center\">6个低浓度样品绝对差值的平均值</td><td>| ¯d |</td><td>{$six_avg}</td></tr>";
        $html .= "<tr><td align=\"center\">极差系数</td><td>C</td><td>{$guding}</td></tr>";
        $html .= "<tr><td align=\"center\">恒定标准差</td><td>α</td><td>{$hdbzc}</td></tr>";
        $html.='</table>';
        $html.= "<br><br>";

        $html.= '<table class="layui-table layui-table-form">';
        $html.= "<tr><td align=\"center\">相对差值的绝对值的平均值</td><td>|¯dj|rel</td><td>{$ten_avg}</td></tr>";
        $html.= "<tr><td align=\"center\">相对恒定标准差</td><td>β</td><td>{$xdhdbzc}</td></tr>";
        $html.='</table>';

        $html.= "<br><br>";
        $html.= '<table class="layui-table layui-table-form">';
        $html.= "<tr><td align=\"center\">试样转基因含量</td><td>c</td><td>{$jyhl}</td></tr>";
        $html.= "<tr><td align=\"center\">测量不确定度</td><td>u<sub>p</sub></td><td>{$clbqdd}</td></tr>";
        $html.='</table>';
        echo $html;
    }

    //导出export_data
    public function export_data(){

        $where=[];
        $where[]=['uid','=',get_login_admin('id')];
        $jingdu=JingmiduCg::where($where)->order('id asc')->select();
        $i=0;
        $j=0;
        $six_array=[];
        $ten_array=[];
        $jingdu1=$jingdu;
        foreach($jingdu1 as $key=>$v){
            $i++;
            if($i<=6){
                $six_array[]=$v['jueduizhi'];
            }else{
                continue;
            }
        }
        foreach($jingdu as $key=>$v){
            $j++;
            if($j>=7){
                $ten_array[]=$v['xiangduizhi'];
            }else{
                continue;
            }
        }
        //=AVERAGE(D4:D9)
        $six_avg=sprint_data(Statistical::AVERAGE($six_array),3);
        $guding=1.128;//固定值
        //=I6/I7
        $hdbzc=sprint_data($six_avg/$guding,3);

        $ten_avg=sprint_data(Statistical::AVERAGE($ten_array),3);
        //=AVERAGE(F10:F18)
        //=I11/I7
        $xdhdbzc=sprint_data($ten_avg/$guding,3);

        $jyhl=number_format(1.5001,3);
        Log::info('jyhl='.$jyhl);
        //=(I8^2+(I12*I15)^2)^0.5
        $clbqdd=pow(pow($hdbzc,2)+pow(($xdhdbzc*$jyhl),2),0.5);
        $clbqdd=sprint_data($clbqdd,3);

        //导出表格
        $objExcel = new Spreadsheet();
        $objWriter = IOFactory::createWriter($objExcel, 'Xlsx');
        $objActSheet = $objExcel->getActiveSheet(0);
        $objActSheet->setTitle('用常规样品评定测量方法精密度不确定度');//设置excel的标题

        //$objActSheet->getStyle('A1:C1')->getFont()->setSize(14);
        //$objActSheet->getStyle('A1:C1')->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setRGB('CCCCCC');
        // 设置列宽
        $objActSheet->getColumnDimension('A')->setWidth(50);
        $objActSheet->getColumnDimension('B')->setWidth(20);
        $objActSheet->getColumnDimension('C')->setWidth(20);

        $objActSheet->getRowDimension(1)->setRowHeight(25);      // 设置行高

        //设置水平、垂直居中
        $styleArray = [
            //设置字体样式
            'font' => [
                'name' => 'Arial',
                //'bold' => true,
                //'italic' => false,
                //'underline' => Font::UNDERLINE_DOUBLE,
                //'strikethrough' => false,
                'color' => [
                    'rgb' => Color::COLOR_BLACK
                ]
            ],
            //设置边框线样式
            'borders' => [
                //allBorders所有的边框线样式
                //左边框线
                /*'bottom' => [
                    'borderStyle' => Border::BORDER_DASHDOT,
                    'color' => [
                        'rgb' => '808080'
                    ]
                ],*/
                //上边框线
                /*'top' => [
                    'borderStyle' => Border::BORDER_DASHDOT,
                    'color' => [
                        'rgb' => '808080'
                    ]
                ]*/
            ],
            //对齐样式
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_CENTER,
                'vertical' => Alignment::VERTICAL_CENTER,
                'wrapText' => true,
            ],
            //是否使用前缀
            'quotePrefix'    => true
        ];
        //表格1
        $objActSheet->getStyle('A1:C3')->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN);       // 下边框样式
        $objActSheet->getStyle('A1:C3')->applyFromArray($styleArray);

        $objActSheet->setCellValue('A1', '6个低浓度样品绝对差值的平均值');
        $objActSheet->setCellValue('B1', '| ¯d |');
        $objActSheet->setCellValue('C1', $six_avg);
        $objActSheet->getRowDimension(1)->setRowHeight(25);

        $objActSheet->setCellValue('A2', '极差系数');
        $objActSheet->setCellValue('B2', 'C');
        $objActSheet->setCellValue('C2', $guding);
        $objActSheet->getRowDimension(2)->setRowHeight(25);

        $objActSheet->setCellValue('A3', '恒定标准差');
        $objActSheet->setCellValue('B3', 'α');
        $objActSheet->setCellValue('C3', $hdbzc);
        $objActSheet->getRowDimension(3)->setRowHeight(25);

        //表格2
        $objActSheet->getStyle('A6:C7')->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN);       // 下边框样式
        $objActSheet->getStyle('A6:C7')->applyFromArray($styleArray);

        $objActSheet->setCellValue('A6', '相对差值的绝对值的平均值');
        $objActSheet->setCellValue('B6', '|¯dj|rel');
        $objActSheet->setCellValue('C6', $ten_avg);
        $objActSheet->getRowDimension(6)->setRowHeight(25);

        $objActSheet->setCellValue('A7', '相对恒定标准差');
        $objActSheet->setCellValue('B7', 'β');
        $objActSheet->setCellValue('C7', $xdhdbzc);
        $objActSheet->getRowDimension(7)->setRowHeight(25);

        //表格3
        $objActSheet->getStyle('A10:C11')->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN);       // 下边框样式
        $objActSheet->getStyle('A10:C11')->applyFromArray($styleArray);

        $objActSheet->setCellValue('A10', '试样转基因含量');
        $objActSheet->setCellValue('B10', 'c');
        //$objActSheet->setCellValue('C10', $jyhl);
        //设置单元格文本类型 TYPE_STRING ，数字 TYPE_NUMERIC
        $objActSheet->getCell('C10')->setValueExplicit($jyhl, DataType::TYPE_STRING);

        $objActSheet->getRowDimension(10)->setRowHeight(25);

        $objActSheet->setCellValue('A11', '测量不确定度');
        $objActSheet->setCellValue('B11', 'up');
        //$objActSheet->setCellValue('C11', $clbqdd);
        $objActSheet->getCell('C11')->setValueExplicit($clbqdd, DataType::TYPE_STRING);
        $objActSheet->getRowDimension(11)->setRowHeight(25);

        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="用常规样品评定测量方法精密度不确定度报表（'. date('Y-m-d')  .'）'.'.xlsx"');
        header('Cache-Control: max-age=0');
        $objWriter->save('php://output');
    }

    //删除数据
    public function delete(){
        //Db::name('sjjy')->where('id','>',0)->delete();
        $id = get_params("id");
        if(empty($id)){
            return to_assign(1, "id不能为空");
        }
        // 软删除
        $db=JingmiduCg::where('id',$id)->find();
        if($db){
            // 软删除
            $db->delete();
            add_log('delete', $id);
            return to_assign(0, "删除成功");
        } else {
            return to_assign(1, "删除失败");
        }
    }

    //清空数据
    public function delete_all(){
        // 软删除
        $param = get_params();
        $db=JingmiduCg::where('uid',get_login_admin('id'))->delete();
        if($db){

            return to_assign(0, "删除成功");
        } else {
            return to_assign(1, "删除失败");
        }
    }
}